﻿//0. Parse Deepest Depth thanh Interval
//1. Default Value cho ServiceRun
//2. Implement CBL JobType
//3. Truong hop Driller Depth they put in 4146.00 m. thi sao ? Luc nay tot nhat la read ve Text roi Parse Number inside Text
//4. Unit Test cho Constructor FarPac sao cho luc chay chon thu muc chua cac File Excel de Test.
//5. Everytime Parse FAIL should MessageBox Retry or Skip so User can fix Excel.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;
using OfficeLib = DienLib.OfficeLib;

namespace DienLib.ReportLib
{

    public class FarPac
    {
        //Excel.Application _app = null;
        //Excel.Workbook _book = null;
        //Excel.Worksheet _sheetMaster = null;
        //Excel.Worksheet _sheetReport = null;
        //Excel.Worksheet _sheetCusSat = null;
        //Excel.Worksheet _sheetTicket1 = null;
        //Excel.Worksheet _sheetEXP = null;
        //Excel.Worksheet _sheetTRFA = null;
        //Excel.Worksheet _sheetIR1 = null;

        DienLib.ReportLib.Configuration _config = null;

        #region Farpac Info
        const string _CELL_MODIFIEDDATE = "A5";
        DateTime _ModifiedDate;
        const string _CELL_TICKET = "B8";
        string _Ticket;
        const string _CELL_DATE = "B9";
        DateTime _Date;
        const string _CELL_FIELD = "B10";
        string _Field;
        const string _CELL_LOCATION = "B11";
        string _Location;
        const string _CELL_PROVINCE = "B12";
        string _Province;
        const string _CELL_COUNTRY = "B13";
        string _Country;
        const string _CELL_WELLNO = "B14";
        string _WellNo;
        const string _CELL_CLIENT = "B15";
        string _Client;
        const string _CELL_PLATFORM = "B16";
        string _Platform;
        const string _CELL_UNITNO = "B17";
        string _UnitNo;
        const string _CELL_BOM = "B18";
        EnumsStructs.JobBOM _BOM;

        const string _CELL_ENGINEER1 = "B20";
        string _Engineer1;
        const string _CELL_ENGINEER1SAP = "B21";
        string _Engineer1SAP;
        const string _CELL_ENGINEER2 = "B22";
        string _Engineer2;
        const string _CELL_ENGINEER2SAP = "B23";
        string _Engineer2SAP;
        const string _CELL_CREWCHIEF1 = "B28";
        string _CrewChief1;
        const string _CELL_CREWCHIEF2 = "B33";
        string _CrewChief2;
        const string _CELL_OPERATOR1 = "B29";
        string _Operator1;
        const string _CELL_OPERATOR2 = "B30";
        string _Operator2;
        const string _CELL_OPERATOR5 = "B34";
        string _Operator5;
        const string _CELL_OPERATOR6 = "B35";
        string _Operator6;
        const string _CELL_COMAN1 = "B48";
        string _CompanyMan1;
        const string _CELL_COMAN2 = "B49";
        string _CompanyMan2;
        const string _CELL_JOBTYPE = "B54";
        string _JobType;
        const string _CELL_HOLETYPE = "B55";
        string _HoleType;
        const string _CELL_DRILLDEPTH = "B56";
        double _DrillerDepth;
        const string _CELL_JOBMD = "B57";
        double _JobMD;
        const string _CELL_JOBTVD = "B58";
        double _JobTVD;
        const string _CELL_CASINGSIZE = "B59";
        double _CasingSize;
        const string _CELL_CASINGDEPTH = "B60";
        double _CasingDepth;
        const string _CELL_MAXBHT = "B61";
        double _MaxBHT;
        const string _CELL_FINALWHP = "B62";
        double _FinalWHP;
        const string _CELL_WELLFLUID = "B63";
        string _WellFluid;
        const string _CELL_FLUIDGRAVITY = "B64";
        double _FluidGravity;
        const string _CELL_FLUIDLEVEL = "B65";
        double _FluidLevel;
        const string _CELL_BITSIZE = "B66";
        double _BitSize;
        const string _CELL_MINIMUMID = "B67";
        double _MinID;
        const string _CELL_MAXDEVIATION = "B68";
        double _MaxDev;
        const string _CELL_MAXDEVDEPTH = "B69";
        double _MaxDevDepth;
        const string _CELL_KICKOFFPOINT = "B70";
        double _KickOffPoint;
        const string _CELL_MAXDOGLEGRATE = "B71";
        double _MaxDogLegRate;
        const string _CELL_MAXDOGLEGRATEDEPTH = "B72";
        double _MaxDogLegRateDepth;
        const string _CELL_DEVTD = "B73";
        double _DevTD;
        const string _CELL_CORRLOG_DESCRIPTION = "B74";
        string _CorrLogDescription;
        const string _CELL_CORRLOG_DATE = "B75";
        DateTime _CorrLogDate;
        const string _CELL_DIRECTION_TD = "B76";
        double _DirectionTD;

        const string _CELL_SERVICERUN_MASTER = "B78:B194";
        const string _CELL_SERVICERUN_REPORT_P1 = "E54:E87";
        const string _CELL_SERVICERUN_REPORT_P2 = "E97:E135";
        const string _CELL_SERVICERUN_REPORT_P3 = "E143:E183";
        const string _CELL_SERVICERUN_REPORT_P4 = "E191:E231";
        const string _CELL_SERVICERUN_REPORT_P5 = "E239:E279";
        const string _CELL_SERVICERUN_TICKET = "A20:A49";
        List<EnumsStructs.ServiceRun> ServiceRuns;
        //------------------------------------------//
        const string _CELL_CABLEDRUM_NO = "B196";
        string _CableDrumNo;
        const string _CELL_WIRELINE_NO = "B197";
        string _WirelineNo;
        const string _CELL_CABLETYPE = "B198";
        string _CableType;
        const string _CELL_STARTLENGTH = "B199";
        double _StartLength;
        const string _CELL_CUTLENGTH = "B200";
        double _CutLength;
        const string _CELL_ENDLENGTH = "B201";
        double _EndLength;
        const string _CELL_PREVIOUS_RUN = "B202";
        int _PreviousRuns;
        const string _CELL_RUN_THIS_JOB = "B203";
        int _RunThisJob;
        const string _CELL_TOTAL_RUN = "B204";
        int _TotalRuns;
        //------------------------------------------//
        const string _CELL_KPI_SURVEY_CONDUCTED_DATE = "X17";
        DateTime _KPI_SurveyDate;
        const string _CELL_KPI_JOBACHIEVED = "X18";
        bool _KPI_JobAchieved;
        const string _CELL_KPI_TOTAL_OPR_TIME = "X19";
        DateTime _KPI_TotalOperateTime;
        const string _CELL_KPI_INCIDENT = "X20";
        bool _KPI_Incident;
        const string _CELL_KPI_LOSTTIME = "X21";
        DateTime _KPI_LostTime;
        const string _CELL_KPI_URF_LOSTITME = "X22";
        DateTime _KPI_URFLostTime;
        const string _CELL_KPI_NUMSERVICES = "X23";
        int _KPI_NumServices;
        const string _CELL_KPI_NUMSERVICES_LOSTTIME = "X24";
        int _KPI_NumServicesLostTime;
        const string _CELL_NUMFAILURES_LOSTTIME = "X25";
        int _KPI_NumFailuresLostTime;
        const string _CELL_KPI_TOTAL_RUN = "X26";
        int _KPI_TotalRuns;
        const string _CELL_KPI_TOTAL_RUN_LOSTITME = "X27";
        int _KPI_TotalRunsLostTime;
        const string _CELL_KPI_PERFFOOT_RUN = "X28";
        double _KPI_PerfFootageRun;
        const string _CELL_KPI_PERFFOOT_DELIVER = "X29";
        double _KPI_PerfFootageDeliver;
        const string _CELL_KPI_PERFFOOT_DIFFERENCE = "X30";
        double _PerfFootageDiff;


        const string _CELL_TICKET1_SUBTOTAL = "J50";
        double _Ticket1_SubTotal;
        const string _CELL_TICKET1_TOTAL_PREDISCOUNT = "J51";
        double _Ticket1_TotalPreDiscount;
        const string _CELL_TICKET1_DISCOUNT = "J52";
        double _Ticket1_Discount;
        const string _CELL_TICKET1_TOTAL = "J53";
        double _Ticket1_Total;
        #endregion

        #region Summit Info
        public SummitLib.Enums.Customer Customer { get; private set; }
        public SummitLib.Enums.CompanyMan CompanyMan { get; private set; }

        public SummitLib.Enums.WellType WellType { get; private set; }
        public string SaleNumber { get; private set; }
        public SummitLib.Enums.ServiceCenter PrimaryServiceCenter { get; private set; }
        public SummitLib.Enums.ServiceCenter SecondaryServiceCenter { get; private set; }
        public SummitLib.Enums.JobType JobType { get; private set; }

        List<SummitLib.Enums.CompanyMan> _sCompanyMen;
        public SummitLib.Enums.CompanyMan[] CompanyMen
        {
            get { return _sCompanyMen.ToArray(); }
        }

        public string WellName { get; private set; }
        public SummitLib.Enums.Location Location { get; private set; }
        public SummitLib.Enums.Country Country { get; private set; }
        public SummitLib.Enums.ServiceCoordinator ServiceCoordinator { get; private set; }
        public SummitLib.Enums.WellFluid WellFluid { get; private set; }
        public SummitLib.Enums.Unit.Style UnitStyle { get; private set; }
        public string DrillDepth { get; private set; }
        public string JobMD { get; private set; }
        public string JobTVD { get; private set; }
        public string MaxTemp { get; private set; }
        public string FormationPressure { get; private set; }
        public string WellheadPressure { get; private set; }
        public string FluidLevel { get; private set; }
        public string BitSize { get; private set; }
        public string MinID { get; private set; }
        public string MinIDDepth { get; private set; }
        public string MaxDeviation { get; private set; }
        public string MaxDevDepth { get; private set; }
        public string KickoffDepth { get; private set; }
        public string MaxDogleg { get; private set; }
        public string MaxDoglegDepth { get; private set; }
        public string WellDirectionTD { get; private set; }
        public string WellDeviationTD { get; private set; }
        public string WaterDepth { get; private set; }
        public string SeaBedTemp { get; private set; }
        public string ShipTo { get; private set; }

        List<SummitLib.Enums.LoggingEngineer> _sLoggingEngineers;
        public SummitLib.Enums.LoggingEngineer LoggingEngineer { get; private set; }

        List<SummitLib.PERFROW> _sPERFROW;
        public SummitLib.PERFROW[] PerfRuns { get; private set; }

        List<SummitLib.SERVICEROW> _sNONPERFROW;
        public SummitLib.SERVICEROW[] NonPerfRuns { get; private set; }

        #endregion

        public FarPac(string FileName, DienLib.ReportLib.Configuration config)
        {
            Excel.Workbook _book = null;
            Excel.Worksheet _sheetMaster = null;
            Excel.Worksheet _sheetReport = null;
            Excel.Worksheet _sheetCusSat = null;
            Excel.Worksheet _sheetTicket1 = null;
            Excel.Worksheet _sheetEXP = null;

            _config = config;
            if (FileName == "") throw new InvalidOperationException("Can not Open a blank filename !");

            // Open Excel File
            try
            {
                _book = OfficeLib.Excel.BookOpen(FileName, true);
            }
            catch (Exception e) { PopUp.PopUpOK.ShowDialog(e.Message, "Error 0xDbRbFc : Cannot Open Farpac File"); }

            // Parse Worksheet
            foreach (Excel.Worksheet sheet in _book.Worksheets)
            {
                string txt = sheet.Name.ToLower();
                if (txt.Contains("master")) _sheetMaster = sheet;
                else if (txt.Contains("report")) _sheetReport = sheet;
                else if (txt.Contains("sat")) _sheetCusSat = sheet;
                else if (txt.Contains("ticket 1")) _sheetTicket1 = sheet;
                else if (txt.Contains("exp")) _sheetEXP = sheet;
            }

            // Parse Cell
            try
            {
                try { _ModifiedDate = DienLib.ReportLib.Command.ParseDate(getText(_sheetMaster.Range[_CELL_MODIFIEDDATE])); }
                catch (Exception ex) { PopUp.PopUpOK.ShowDialog(ex.Message, "\"CANNOT RECOGNIZE REVISION DATE !\""); }

                //============================================================//
                _Ticket = getText(_sheetMaster.Range[_CELL_TICKET]);
                SaleNumber = _Ticket;
                //============================================================//

                _Date = getValue<DateTime>(_sheetMaster.Range[_CELL_DATE], "Job Finish Date");

                //============================================================//
                _Field = getText(_sheetMaster.Range[_CELL_FIELD]);
                if (_Field == "BONGKOT") { WaterDepth = "74.8"; SeaBedTemp = "75"; Location = SummitLib.Enums.Location.FEDERAL_WATER; }
                else if (_Field == "BONGKOT SOUTH") { WaterDepth = "74.9"; SeaBedTemp = "76"; Location = SummitLib.Enums.Location.FEDERAL_WATER; }
                else if (_Field == "ARTHIT") { WaterDepth = "75"; SeaBedTemp = "77"; Location = SummitLib.Enums.Location.FEDERAL_WATER; }
                else { WaterDepth = "1"; SeaBedTemp = "1"; }
                //============================================================//

                _Location = getText(_sheetMaster.Range[_CELL_LOCATION]);
                _Province = getText(_sheetMaster.Range[_CELL_PROVINCE]);

                //============================================================//
                _Country = getText(_sheetMaster.Range[_CELL_COUNTRY]);
                if (_Country.ToLower().Contains("thai")) { PrimaryServiceCenter = SecondaryServiceCenter = SummitLib.Enums.ServiceCenter.THAILAND; Country = SummitLib.Enums.Country.THAILAND; }
                else { PrimaryServiceCenter = SecondaryServiceCenter = SummitLib.Enums.ServiceCenter.NA; Country = SummitLib.Enums.Country.THAILAND; }
                //============================================================//
                _WellNo = getText(_sheetMaster.Range[_CELL_WELLNO]);
                WellName = _WellNo; // Infuture Build seperate Enum for WellName. Should take from Configuration File
                //============================================================//
                _Client = getText(_sheetMaster.Range[_CELL_CLIENT]);
                if (_Client.ToLower().Contains("ptt")) { Customer = SummitLib.Enums.Customer.PTTEP; ServiceCoordinator = SummitLib.Enums.ServiceCoordinator.RICHARD; UnitStyle = SummitLib.Enums.Unit.Style.PTTEP; }
                else if (_Client.ToLower().Contains("chevron")) { Customer = SummitLib.Enums.Customer.CHEVRON; ServiceCoordinator = SummitLib.Enums.ServiceCoordinator.BAROTO; UnitStyle = SummitLib.Enums.Unit.Style.CHEVRON; }
                else { Customer = SummitLib.Enums.Customer.NA; ServiceCoordinator = SummitLib.Enums.ServiceCoordinator.NA; }
                //============================================================//

                _Platform = getText(_sheetMaster.Range[_CELL_PLATFORM]); // Not use in Summit
                _UnitNo = getText(_sheetMaster.Range[_CELL_UNITNO]); // Just use in UnitNo
                _BOM = EnumsStructs.eJobBOM.ToEnum(getText(_sheetMaster.Range[_CELL_BOM]));
                //public enum JobBOM { NA, PERF, PLT, CBL, TMDL, PIPE_RECOVER, BRIDGE_PLUG, PRESSURE_TEMP, SURFACE_TEMP };
                //public enum JobType { NA, PERF }; // Transfer from JobBOM to JobType
                if (_BOM == EnumsStructs.JobBOM.NA) JobType = SummitLib.Enums.JobType.NA;
                else if (_BOM == EnumsStructs.JobBOM.PERF) JobType = SummitLib.Enums.JobType.PERF;
                else if (_BOM == EnumsStructs.JobBOM.CBL) JobType = SummitLib.Enums.JobType.CBL;
                else
                {
                    PopUp.PopUpOK.ShowDialog("Farpac Job BOM is different than PERF. but will choose \"7548 - PERFORATING - OTHERS BOM\" in Summit Package", "Please NOTE");
                }

                //============================================================//

                _Engineer1 = getText(_sheetMaster.Range[_CELL_ENGINEER1]);
                _Engineer1SAP = getText(_sheetMaster.Range[_CELL_ENGINEER1SAP]);
                _Engineer2 = getText(_sheetMaster.Range[_CELL_ENGINEER2]);
                _Engineer2SAP = getText(_sheetMaster.Range[_CELL_ENGINEER2SAP]);

                _sLoggingEngineers = new List<SummitLib.Enums.LoggingEngineer>();
                _sLoggingEngineers.AddRange(getEngineer(_Engineer1));
                _sLoggingEngineers.AddRange(getEngineer(_Engineer2));
                if (_sLoggingEngineers.Count != 0) LoggingEngineer = _sLoggingEngineers.Last(); // First Engineer is default Engineer
                else LoggingEngineer = SummitLib.Enums.LoggingEngineer.NA; // Engineer khong co ai het

                //============================================================//

                _CrewChief1 = getText(_sheetMaster.Range[_CELL_CREWCHIEF1]);
                _Operator1 = getText(_sheetMaster.Range[_CELL_OPERATOR1]);
                _Operator2 = getText(_sheetMaster.Range[_CELL_OPERATOR2]);
                _CrewChief2 = getText(_sheetMaster.Range[_CELL_CREWCHIEF2]);
                _Operator5 = getText(_sheetMaster.Range[_CELL_OPERATOR5]);
                _Operator6 = getText(_sheetMaster.Range[_CELL_OPERATOR6]);

                //============================================================//
                _CompanyMan1 = getText(_sheetMaster.Range[_CELL_COMAN1]);
                _CompanyMan2 = getText(_sheetMaster.Range[_CELL_COMAN2]);

                _sCompanyMen = new List<SummitLib.Enums.CompanyMan>();
                _sCompanyMen.AddRange(getCoMan(_CompanyMan1));
                _sCompanyMen.AddRange(getCoMan(_CompanyMan2));
                if (_sCompanyMen.Count != 0) CompanyMan = _sCompanyMen.Last(); // Last companyman is default CoMan
                else CompanyMan = SummitLib.Enums.CompanyMan.NA; // Company Man khong co ai het
                //============================================================//
                _JobType = getText(_sheetMaster.Range[_CELL_JOBTYPE]);
                if (!_JobType.ToLower().Contains("ch"))
                {
                    PopUp.PopUpOK.ShowDialog("We currently support only \"CH Farpac\", if this is CH Farpac then look Master List Cell \"B54\" to see if CH is there ?", "UNSUPPORTED FARPAC !");
                    throw new InvalidOperationException("Support CH Farpac only, check \"B54\" Cell if CH is there");
                }
                //============================================================//

                _HoleType = getText(_sheetMaster.Range[_CELL_HOLETYPE]); // DEVIATED

                //============================================================//
                _DrillerDepth = getValue<double>(_sheetMaster.Range[_CELL_DRILLDEPTH], "Driller Depth");
                DrillDepth = _DrillerDepth.ToString();
                //============================================================//
                _JobMD = getValue<double>(_sheetMaster.Range[_CELL_JOBMD], "Job MD");
                JobMD = _JobMD.ToString();
                //============================================================//
                _JobTVD = getValue<double>(_sheetMaster.Range[_CELL_JOBTVD], "Job TVD");
                JobTVD = _JobTVD.ToString();
                //============================================================//

                _CasingSize = getValue<double>(_sheetMaster.Range[_CELL_CASINGSIZE], "Casing Size");
                _CasingDepth = getValue<double>(_sheetMaster.Range[_CELL_CASINGDEPTH], "Casing Depth");

                //============================================================//
                _MaxBHT = getValue<double>(_sheetMaster.Range[_CELL_MAXBHT], "Max BHT");
                MaxTemp = _MaxBHT.ToString();
                //============================================================//
                _FinalWHP = getValue<double>(_sheetMaster.Range[_CELL_FINALWHP], "Final WHP");
                WellheadPressure = _FinalWHP.ToString();
                //============================================================//

                PopUp.PopUpOK.ShowDialog("No \"Formation Pressure\" in Farpac, will put \"0000psi\"", "Missing Farpac Info. for Summit"); FormationPressure = "0";

                //============================================================//
                _WellFluid = getText(_sheetMaster.Range[_CELL_WELLFLUID]);
                WellFluid = getWellFluid(_WellFluid);
                WellType = getWellType(WellFluid);
                if (WellType == SummitLib.Enums.WellType.NA) WellType = ReportLib.PopUpWellType.ShowDialog();
                //============================================================//
                string st = getText(_sheetMaster.Range[_CELL_FLUIDGRAVITY]); // Call this highlight already then later no highlight cell anymore
                if (Regex.IsMatch(st, @"[0-9].*[/\\\|&-].*[0-9]"))
                {   // Multiple Gravity. Try to Parse & get Bigger number (Brine priority). Will Implement Excel Drop Down List with Auto Choosen Gravity very soon
                    foreach (string group in DienLib.RegexD.Get2GroupSeperateBy(st, @"[/\\\|&-]")) // Note : Test add : become @"[/\\\|&-]" fail. don't know why
                    {
                        _FluidGravity = Math.Max(DienLib.RegexD.GetDoubleFromString(group), _FluidGravity);
                    }
                }
                else _FluidGravity = getValue<double>(_sheetMaster.Range[_CELL_FLUIDGRAVITY], "Fluid Gravity", false);
                //============================================================//
                st = getText(_sheetMaster.Range[_CELL_FLUIDLEVEL]).ToLower(); // Call this highlight already then later no highlight cell anymore
                if (Regex.IsMatch(st, "full|surface")) FluidLevel = "0";
                else
                {
                    _FluidLevel = getValue<double>(_sheetMaster.Range[_CELL_FLUIDLEVEL], "Fluid Level", false);
                    FluidLevel = _FluidLevel.ToString();
                }
                //============================================================//
                _BitSize = getValue<double>(_sheetMaster.Range[_CELL_BITSIZE], "Bit Size");
                BitSize = _BitSize.ToString();
                //============================================================//
                _MinID = getValue<double>(_sheetMaster.Range[_CELL_MINIMUMID], "Min ID");
                MinID = _MinID.ToString();
                //============================================================//

                PopUp.PopUpOK.ShowDialog("No \"Depth of Minimum ID\" in Farpac, will put \"000m\"", "Missing Farpac Info. for Summit"); MinIDDepth = "0";

                //============================================================//
                _MaxDev = getValue<double>(_sheetMaster.Range[_CELL_MAXDEVIATION], "Max Deviation");
                MaxDeviation = _MaxDev.ToString();
                //============================================================//
                _MaxDevDepth = getValue<double>(_sheetMaster.Range[_CELL_MAXDEVDEPTH], "Max Deviation Depth");

                MaxDevDepth = _MaxDevDepth.ToString();
                //============================================================//
                _KickOffPoint = getValue<double>(_sheetMaster.Range[_CELL_KICKOFFPOINT], "Kickoff Point");
                KickoffDepth = _KickOffPoint.ToString();
                //============================================================//
                _MaxDogLegRate = getValue<double>(_sheetMaster.Range[_CELL_MAXDOGLEGRATE], "Max Dogleg Rate");
                MaxDogleg = _MaxDogLegRate.ToString();
                //============================================================//
                _MaxDogLegRateDepth = getValue<double>(_sheetMaster.Range[_CELL_MAXDOGLEGRATEDEPTH], "Max Dogleg Rate Depth");
                MaxDoglegDepth = _MaxDogLegRateDepth.ToString();
                //============================================================//
                _DevTD = getValue<double>(_sheetMaster.Range[_CELL_DEVTD], "Deviation TD");
                WellDeviationTD = _DevTD.ToString();
                //============================================================//

                _CorrLogDescription = getText(_sheetMaster.Range[_CELL_CORRLOG_DESCRIPTION]);
                _CorrLogDate = getValue<DateTime>(_sheetMaster.Range[_CELL_CORRLOG_DATE], "Correlation Log Description");

                //============================================================//
                _DirectionTD = getValue<double>(_sheetMaster.Range[_CELL_DIRECTION_TD], "Direction at TD");
                WellDirectionTD = _DirectionTD.ToString();
                //============================================================//
                //const string _CELL_SERVICERUN_MASTER = "B78:B194";
                //const string _CELL_SERVICERUN_REPORT_P1 = "E54:E87";
                //const string _CELL_SERVICERUN_REPORT_P2 = "E97:E135";
                //const string _CELL_SERVICERUN_REPORT_P3 = "E143:E183";
                //const string _CELL_SERVICERUN_REPORT_P4 = "E191:E231";
                //const string _CELL_SERVICERUN_REPORT_P5 = "E239:E279";
                //const string _CELL_SERVICERUN_TICKET = "A20:A49";

                ServiceRuns = new List<EnumsStructs.ServiceRun>();
                Excel.Range _RangeServiceRun = _sheetMaster.Range[_CELL_SERVICERUN_MASTER];
                for (int i = 1, j = 1; i < (int)_RangeServiceRun.Rows.Count; i += 3, j++)
                {
                    Excel.Range _CellServiceRun = _RangeServiceRun[i, 1] as Excel.Range;
                _RETRY_NAME:
                    // Neu khong co Service Run Name thi luu lai, vong lap sau ma thay thi bao loi
                    string _Name = OfficeLib.Excel.CellText(_CellServiceRun).Trim();
                    if (!string.IsNullOrEmpty(_Name)) HighlightCell(_CellServiceRun);
                // MORE CODE to Parse _Name HERE to get Type : PERF or GC or TMDL or CBL ...
                // with corresponding Struct for each kind

                    //=========================================================================================================//
                // Neu khong co Deepest Depth ma co Service Run Name thi bao loi
                _RETRY_DEPTH:
                    string _DeepestDepth = OfficeLib.Excel.CellText(_CellServiceRun.Offset[1, 0]).Trim();
                    if (!string.IsNullOrEmpty(_DeepestDepth) | !string.IsNullOrEmpty(_Name)) HighlightCell(_CellServiceRun.Offset[1, 0]); // Neu khong null hoac null ma co Name thi Highligt
                    if (_DeepestDepth == "" & _Name != "")
                    {   // Forget  Deepest Depth (Have Name)
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You forget to put \"Deepest Depth\" for this Run . We will auto put \"1\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_DEPTH;
                        _DeepestDepth = "1";
                    }
                    else if (_DeepestDepth != "" & _Name == "")
                    {   // Have Deepest Depth but No Name
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You have to add \"Service Run Name\" or Delete \"Deepest Depth\" for this Run. We will leave \"\" BLANK or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_NAME;
                        _DeepestDepth = "";
                    }
                //=========================================================================================================//
                // Khong quan trong, neu khong phai so double thi cho N/A
                _RETRY_TEMP:
                    Excel.Range _rangeMaxTemp = _CellServiceRun.Offset[2, 0];
                    double _MaxTemp = -1;
                    if (OfficeLib.Excel.FunctionIsNumber(_book.Application.WorksheetFunction, _rangeMaxTemp)) { _MaxTemp = getValue<double>(_rangeMaxTemp, "Max Temp on Run"); }
                    if (Regex.IsMatch(OfficeLib.Excel.CellText(_rangeMaxTemp), "n*a", RegexOptions.IgnoreCase)) _MaxTemp = 1; // N/A maxtemp = 1
                    if (!(_MaxTemp == -1) | !string.IsNullOrEmpty(_Name)) HighlightCell(_CellServiceRun.Offset[2, 0]);
                    if (_MaxTemp == -1 & _Name != "")
                    {   // Forget MaxTemp (Have Name)
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You forget to put \"Max Temp\" for this Run. We will auto put \"1\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_TEMP;
                        _MaxTemp = 1;
                    }
                    else if (_MaxTemp != -1 & _Name == "")
                    {   // Have MaxTemp but NoName => Delete Max Temp
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You have to add \"Service Run Name\" or Delete \"Max Temp\" for this Run. We will leave \"-1\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_NAME;
                        _MaxTemp = -1;
                    }
                //=========================================================================================================//
                // Neu khong co status thi MessageBox va cho mac dinh la OK. Neu co ma khong co Name thi bao loi
                _RETRY_STATUS:
                    string _Status = OfficeLib.Excel.CellText(_CellServiceRun.Offset[0, 1]).Trim();
                    if (!string.IsNullOrEmpty(_Status) | !string.IsNullOrEmpty(_Name)) HighlightCell(_CellServiceRun.Offset[0, 1]);
                    if (_Status == "" & _Name != "")
                    {   // Forget _Status (Have name)
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You forget to put \"Status\" for this Run. We will auto put \"OK\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_STATUS;
                        _Status = "OK";
                    }
                    else if (_Status != "" & _Name == "")
                    {   // Have Status (No Name) : Delete _Status
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You have to add \"Service Run Name\" or Delete \"Status\" for this Run. We will leave \"\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_NAME;
                        _Status = "";
                    }
                //=========================================================================================================//
                // Yeu cau phai cung ton tai voi Service Name
                _RETRY_ENGINEER:
                    string _Engineer = OfficeLib.Excel.CellText(_CellServiceRun.Offset[0, 2]).Trim();
                    if (!string.IsNullOrEmpty(_Status) | !string.IsNullOrEmpty(_Name)) HighlightCell(_CellServiceRun.Offset[0, 2]);
                    if (_Engineer == "" & _Name != "")
                    {   // Forget _Engineer (Have Name)
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You forget to put \"Engineer Name\" for this Run. We will auto put \"N/A\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_ENGINEER;
                        _Engineer = "N/A";
                    }
                    else if (_Engineer != "" & _Name == "")
                    {   // Have Engineer (Forget Name) : Delete _Engineer
                        PopUp.PopUpResult res = PopUpRetry.ShowDialog("You have to add \"Service Run Name\" or Delete \"_Engineer\" for this Run. We will leave \"\" or you can \"Retry\"", string.Format("Run {0} : {1}", j.ToString(), _Name));
                        if (res == PopUp.PopUpResult.RETRY) goto _RETRY_NAME;
                        _Engineer = "";
                    }
                    //=========================================================================================================//
                    // Add ServiceRuns
                    if (_Name != "") ServiceRuns.Add(new EnumsStructs.ServiceRun(_Name, _DeepestDepth, _MaxTemp, _Status, _Engineer, (uint)j));
                    //=========================================================================================================//
                }
                uint ci = 1; _sPERFROW = new List<SummitLib.PERFROW>(); _sNONPERFROW = new List<SummitLib.SERVICEROW>();
                foreach (EnumsStructs.ServiceRun run in ServiceRuns)
                {   // Parse ServiceRuns to get PERF or NonPERF
                    if (run.Status == EnumsStructs.RunStatus.FIRED_OK)
                    {   // Count if PERF & FIRED OK
                        _sPERFROW.Add(run.ToPERFROW(ci++)); // Add with Gun No increase for next GUN.
                    }
                    else
                    {   // Not PERF or PERF FAIL
                        if (run.Type == EnumsStructs.KnownServiceType.PERF)
                        {   // PERF FAIL
                            _sPERFROW.Add(run.ToPERFROW(ci)); // Add with Gun No not increase because FAIL
                        }
                        else
                        {   // Add Service (Non-Perf) Run
                            _sNONPERFROW.Add(run.ToSERVICEROW());
                        }
                    }
                }


                this.PerfRuns = this._sPERFROW.ToArray();
                this.NonPerfRuns = this._sNONPERFROW.ToArray();
                //============================================================//
                _CableDrumNo = getText(_sheetMaster.Range[_CELL_CABLEDRUM_NO]);
                _WirelineNo = getText(_sheetMaster.Range[_CELL_WIRELINE_NO]);
                _CableType = getText(_sheetMaster.Range[_CELL_CABLETYPE]);
                _StartLength = getValue<double>(_sheetMaster.Range[_CELL_STARTLENGTH], "Cable Start Length");
                _CutLength = getValue<double>(_sheetMaster.Range[_CELL_CUTLENGTH], "Cable Cut Length");
                _EndLength = getValue<double>(_sheetMaster.Range[_CELL_ENDLENGTH], "Cable End Length");
                _PreviousRuns = getValue<int>(_sheetMaster.Range[_CELL_PREVIOUS_RUN], "Cable Previous Runs");
                _RunThisJob = getValue<int>(_sheetMaster.Range[_CELL_RUN_THIS_JOB], "Cable Runs This Job");
                _TotalRuns = getValue<int>(_sheetMaster.Range[_CELL_TOTAL_RUN], "Cable Total Runs");
                //============================================================//

                ShipTo = PopUpShipTo.ShowDialog(_WellNo, _config.ShipTos.ToArray());
            }
            catch (Exception e)
            {
                PopUp.PopUpOK.ShowDialog(e.Message, "Cannot Parse Farpac Correctly !");
            }

            try { OfficeLib.Excel.BookAppClose(_book); }
            catch (Exception ex) { PopUp.PopUpOK.ShowDialog("Something wrong ! Please close Farpac yourself ! \r\n" + ex.Message, "CANNOT CLOSE FARPAC FILE"); return; }

            PopUp.PopUpOK.ShowDialog("Congratulation ! Your Farpac parse successfully !\r\nYou can process \"NEXT STEP\" !", "PARSE FARPAC SUCESSFULLY");
        }


        /// <summary>
        /// This will return the text of range cell already trim both side start & end with Highlighted Cell
        /// </summary>
        /// <param name="range"></param>
        /// <returns>Trimmed Text Return</returns>
        private string getText(Excel.Range range, bool highlight = true)
        {
            if (range == null) throw new InvalidOperationException("range is NULL, can not get Text from it");
            if (highlight) HighlightCell(range);
            return OfficeLib.Excel.CellText(range).Trim();
        }

        /// <summary>
        /// Return Generic Value of cell if empty then return 0 or DateTime.Now
        /// </summary>
        /// <typeparam name="T">Generic Value Type of : DateTime, Double, Int</typeparam>
        /// <param name="range">Cell to get Value</param>
        /// <param name="Title">Value 0.0 for double, 0 for unit &amp; int or DateTime.Now if Cell Blank</param>
        /// <returns></returns>
        private static T getValue<T>(Excel.Range range, string Title, bool highlight = true)
        {
        __RETRY_GETVALUE__:
            if (range == null) throw new InvalidOperationException("range is NULL, can not get Value from it");

            if (highlight) HighlightCell(range);
            string txt = OfficeLib.Excel.CellText(range);
            if (txt.Trim() == "" | Regex.IsMatch(txt, @"n[/\\]a", RegexOptions.IgnoreCase))
            {   // Neu cell Empty hoac N/A thi tra ve gia tri 0 hoac DateTime.No
                PopUp.PopUpResult _pResult = PopUpRetry.ShowDialog(string.Format("Cell \"{0}\" contains \"{1}\" is BLANK or NOT APPLICABLE.\r\n" + "Will auto put ZERO or DateTime.NOW if you click NEXT!\r\n" +
                    "\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", OfficeLib.Excel.CellAddress(range).Replace("$", ""), txt), string.Format("YOU FORGET FILL \"{0}\" !", Title));
                if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                // to FINAL
            }
            else
            {   // Neu cell khong Empty hoac N/A thi Parse
                object value = OfficeLib.Excel.CellValue(range);
                if (typeof(T) == typeof(DateTime)) try { return (T)value; }//(T)(object)Convert.ToDateTime(range.Text);
                    catch (Exception e)
                    {   // Parse DateTime error
                        if (!e.Message.Contains("cast")) throw e;
                        PopUp.PopUpResult _pResult = PopUpRetry.ShowDialog(string.Format("Cannot get DateTime at Cell \"{0}\" contains \"{1}\"\r\n" + "May be you put not correct DateTime Format\r\n" +
                            "\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", OfficeLib.Excel.CellAddress(range).Replace("$", ""), txt), string.Format("YOU FILL \"{0}\" WRONG SYNTAX!", Title));
                        if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                        // to FINAL
                    }
                else if (typeof(T) == typeof(double)) try { return (T)value; }
                    catch (Exception e)
                    {   // Parse Double error
                        if (!e.Message.Contains("cast")) throw e;
                        PopUp.PopUpResult _pResult = PopUpRetry.ShowDialog(string.Format("Cannot get Number at Cell \"{0}\" contains \"{1}\"\r\n" + "May be you put extra Character\r\n" +
                            "Eg: \"0.82 SG\", just put \"0.82\", no need \"SG\".\r\n\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", OfficeLib.Excel.CellAddress(range).Replace("$", ""), txt), string.Format("YOU FILL \"{0}\" WRONG SYNTAX!", Title));
                        if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                        // to FINAL
                    }
                else if (typeof(T) == typeof(int)) try { return (T)(object)Convert.ToInt32(value); }
                    catch (Exception e)
                    {   // Parse int error
                        if (!e.Message.Contains("cast")) throw e;
                        PopUp.PopUpResult _pResult = PopUpRetry.ShowDialog(string.Format("Cannot get Number at Cell \"{0}\" contains \"{1}\"\r\n" + "May be you put extra Character\r\n" +
                            "Eg: \"0.82 SG\", just put \"0.82\", no need \"SG\".\r\n\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", OfficeLib.Excel.CellAddress(range).Replace("$", ""), txt), string.Format("YOU FILL \"{0}\" WRONG SYNTAX!", Title));
                        if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                        // to FINAL
                    }
                else throw new InvalidOperationException("Not Support Value Type T in getValue Function. Only DateTime, Double & Int are supported"); // Not Supported Type, throw Ex
            }

            // FINAL : mean BLANK | N/A | Parse Correct Type but Fail : throw Exception, MessageBox already
            if (typeof(T) == typeof(DateTime)) return (T)(object)DateTime.Now;
            else if (typeof(T) == typeof(double)) return (T)(object)0.0;
            else if (typeof(T) == typeof(int)) return (T)(object)0;
            else throw new InvalidOperationException("Not Support Value Type T in getValue Function. Only DateTime, Double & Int are supported");
        }

        private static SummitLib.Enums.CompanyMan[] getCoMan(string drawCoManString)
        {
            MatchCollection matches = Regex.Matches(drawCoManString, @"[^\\/,;:\|&]+");

            SummitLib.Enums.CompanyMan[] arr = new SummitLib.Enums.CompanyMan[matches.Count];
            for (int i = 0; i < matches.Count; i++)
            {
                arr[i] = SummitLib.Enums.eCompanyMan.ToEnum(matches[i].ToString().Trim());
            }
            return arr;

            /*List<SummitLib.Enums.CompanyMan> listCoMan = new List<SummitLib.Enums.CompanyMan>();
            foreach (Match match in matches)
            {
                //string _match = Regex.Replace(match.ToString(), @"\\|/|,|;|\||&", "");
                listCoMan.Add(SummitLib.Enums.eCompanyMan.ToEnum(match.ToString().Trim()));
            }
            return listCoMan.ToArray();*/
        }

        private static SummitLib.Enums.WellType getWellType(SummitLib.Enums.WellFluid wellfluid)
        //  NA, AIR, BRINE, CRUDE_OIL, DRILL_WATER, FOAM, FRESH_WATER, GLYCOL_BASED, NITROGEN, OIL_BASED, OTHER,
        //  PRODUCED_GAS, PRODUCED_OIL, PRODUCED_WATER, SALT_WATER, SEA_WATER, UNKNOWN, WATER_BASED

            //  WellType { NA, AIR, BRINE, CO2, GAS, HELI, N2, OIL, STEAM, WATER };
        {
            switch (wellfluid)
            {
                case SummitLib.Enums.WellFluid.AIR: return SummitLib.Enums.WellType.AIR;
                case SummitLib.Enums.WellFluid.BRINE: return SummitLib.Enums.WellType.BRINE;
                case SummitLib.Enums.WellFluid.CRUDE_OIL: return SummitLib.Enums.WellType.OIL;
                case SummitLib.Enums.WellFluid.DRILL_WATER: return SummitLib.Enums.WellType.WATER;
                case SummitLib.Enums.WellFluid.FOAM: return SummitLib.Enums.WellType.NA;
                case SummitLib.Enums.WellFluid.FRESH_WATER: return SummitLib.Enums.WellType.WATER;
                case SummitLib.Enums.WellFluid.GLYCOL_BASED: return SummitLib.Enums.WellType.NA;
                case SummitLib.Enums.WellFluid.NA: return SummitLib.Enums.WellType.NA;
                case SummitLib.Enums.WellFluid.NITROGEN: return SummitLib.Enums.WellType.N2;
                case SummitLib.Enums.WellFluid.OIL_BASED: return SummitLib.Enums.WellType.OIL;
                case SummitLib.Enums.WellFluid.OTHER: return SummitLib.Enums.WellType.NA;
                case SummitLib.Enums.WellFluid.PRODUCED_GAS: return SummitLib.Enums.WellType.GAS;
                case SummitLib.Enums.WellFluid.PRODUCED_OIL: return SummitLib.Enums.WellType.OIL;
                case SummitLib.Enums.WellFluid.PRODUCED_WATER: return SummitLib.Enums.WellType.WATER;
                case SummitLib.Enums.WellFluid.SALT_WATER: return SummitLib.Enums.WellType.WATER;
                case SummitLib.Enums.WellFluid.SEA_WATER: return SummitLib.Enums.WellType.WATER;
                case SummitLib.Enums.WellFluid.UNKNOWN: return SummitLib.Enums.WellType.NA;
                case SummitLib.Enums.WellFluid.WATER_BASED: return SummitLib.Enums.WellType.WATER;
                default: return SummitLib.Enums.WellType.NA;
            }
        }

        private static SummitLib.Enums.WellFluid getWellFluid(string drawWellFluid)
        {
            if (Regex.IsMatch(drawWellFluid, @"brine", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.BRINE;
            else if (Regex.IsMatch(drawWellFluid, @"air", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.AIR;
            else if (Regex.IsMatch(drawWellFluid, @"diesel|oil.*base", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.OIL_BASED;
            else if (Regex.IsMatch(drawWellFluid, @"foam", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.FOAM;
            else if (Regex.IsMatch(drawWellFluid, @"water", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.WATER_BASED;
            else if (Regex.IsMatch(drawWellFluid, @"salt", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.SALT_WATER;
            else if (Regex.IsMatch(drawWellFluid, @"sea", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.SEA_WATER;
            else if (Regex.IsMatch(drawWellFluid, @"gas", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.PRODUCED_GAS;
            else if (Regex.IsMatch(drawWellFluid, @"oil", RegexOptions.IgnoreCase)) return SummitLib.Enums.WellFluid.PRODUCED_OIL;
            else return SummitLib.Enums.WellFluid.UNKNOWN;
        }

        private static SummitLib.Enums.LoggingEngineer[] getEngineer(string drawEngineers)
        {
            MatchCollection matches = Regex.Matches(drawEngineers, @"[^\\/,;:\|&]+");

            SummitLib.Enums.LoggingEngineer[] arr = new SummitLib.Enums.LoggingEngineer[matches.Count];
            for (int i = 0; i < matches.Count; i++)
            {
                arr[i] = SummitLib.Enums.eLoggingEngineer.ToEnum(matches[i].ToString().Trim());
            }
            return arr;
        }

        private static void HighlightCell(Excel.Range cell)
        {
            //System.Globalization.CultureInfo Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
            //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            try
            {
                if (OfficeLib.Excel.SheetName(cell.Worksheet) != OfficeLib.Excel.SheetName(cell.Application.ActiveSheet as Excel.Worksheet)) OfficeLib.Excel.SheetSelect(cell.Worksheet);
                OfficeLib.Excel.CellSelect(cell); System.Threading.Thread.Sleep(250);
                //With Activate, if you presently have more than one sheet selected, the ActiveSheet will change, but all selected sheets will remain selected.
                //Select can select multiple objects. Activate can activate only one object. To select a cell or a range of cells, use the Select method. To make a single cell the active cell, use the Activate method.
            }
            catch (Exception e) { throw e; }
            //finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }
    }
}
